﻿Imports Oracle.DataAccess.Client
Imports System.Data.OleDb

Partial Class Comisarias
    Inherits System.Web.UI.Page
    Private x As Integer
    Private Cmd As OracleCommand
    Private dt As Data.DataTable
    Private Cnx As OracleConnection
    Private oradb As String = "Data Source=XE;User Id=SIRA;Password=SIRA123;"
    Private Sql As String
    Private Aux As String
    Private dr As OracleDataReader

    Protected Sub LiberarRecursos()
        Cnx.Close()
        dr.Dispose()
        Cmd.Dispose()
        Cnx.Dispose()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("VarSessionUserIdCategoria") = 3 Then
            btnAgregar.Visible = False
            btnEliminar.Visible = False
        End If
        lblUserNick.Text = Session("VarSessionUserNick")
        lblUserNombre.Text = Session("VarSessionUserNombre")
        lblUserIdCategoria.Text = Session("VarSessionUserIdCategoria")
        lblUserCategoria.Text = Session("VarSessionUserCategoria")
        lblUserIdComisaria.Text = Session("VarSessionUserIdComisaria")
        lblUserComisaria.Text = Session("VarSessionUserComisaria")
        lblUserIdNodo.Text = Session("VarSessionUserIdNodo")
        lblUserNodo.Text = Session("VarSessionUserNodo")
        lblUserIdUsuario.Text = Session("VarSessionUserIdUsuario")
        MostrarTablaComisarias()
    End Sub

    Protected Sub MostrarTablaComisarias()
        Cnx = New OracleConnection(oradb)
        'Sql = "select * from COMISARIAS"
        Sql = "SELECT Comisarias.*, TBL_Nodos.Descripcion FROM TBL_Nodos INNER JOIN Comisarias " & _
              "ON TBL_Nodos.IdNodo = Comisarias.IdNodo and IdComisaria != 1" ' WHERE Comisarias.IdNodo=1"
        Try
            Cnx.Open()
            Cmd = New OracleCommand(Sql, Cnx)
            Cmd.CommandType = Data.CommandType.Text
            dr = Cmd.ExecuteReader()
            If Not IsPostBack Then
                While dr.Read()
                    DDLDescripcion.Items.Add(dr.Item("DESCRIPCION"))
                    DDLEliminar.Items.Add(dr.Item("DESCRIPCION"))
                End While

            End If
            dr = Cmd.ExecuteReader()
            GV2.DataSource = dr
            GV2.DataBind()
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        Finally
        End Try
        LiberarRecursos()
    End Sub

    Protected Sub btnAgregar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAgregar.Click
        PanelSeleccion.Visible = False
        PanelFiltro.Visible = False
        PanelEliminar.Visible = False
        PanelEdicion.Visible = True
        ViewState("Accion") = "Agregar"
        LimpiarPanelEdicion()
        CargarDDLNodos()
    End Sub

    Protected Sub btnEditar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEditar.Click
        PanelSeleccion.Visible = True
        PanelFiltro.Visible = False
        PanelEliminar.Visible = False
        PanelEdicion.Visible = False
        ViewState("Accion") = "Editar"
        If Session("VarSessionUserIdCategoria") = 3 Then
            DDLNodos.Visible = False
        End If
        CargarDDLNodos()
    End Sub
    Protected Sub btnEliminar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEliminar.Click
        PanelSeleccion.Visible = False
        PanelFiltro.Visible = False
        PanelEliminar.Visible = True
        PanelEdicion.Visible = False
        ViewState("Accion") = "Eliminar"
    End Sub
    Protected Sub btnFiltrar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFiltrar.Click
        PanelSeleccion.Visible = False
        PanelFiltro.Visible = True
        PanelEliminar.Visible = False
        PanelEdicion.Visible = False
        ViewState("Accion") = "Filtrar"
        CargarDDLCampos()
    End Sub
    Protected Sub btnAbortar_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles btnSalir.Click, btnSalirPanelEdicion.Click, btnSalirPanelEliminar.Click, btnSalirPanelFiltro.Click
        PanelSeleccion.Visible = False
        PanelEdicion.Visible = False
        PanelEliminar.Visible = False
        PanelFiltro.Visible = False
        ViewState("Accion") = ""
    End Sub
    Protected Sub LimpiarPanelEdicion()
        txtDescripcion.Text = ""
        txtIdNodo.Text = ""
        txtDomicilio.Text = ""
        txtBarrio.Text = ""
        txtTE.Text = ""
        DDLDescripcion.SelectedIndex = 0
    End Sub

    Protected Sub btnRecuperar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRecuperar.Click
        DDLNodos.SelectedIndex = 0
        txtDescripcion.Text = ViewState("Descripcion")
        txtIdNodo.Text = ViewState("IdNodo")
        txtDomicilio.Text = ViewState("Domicilio")
        txtBarrio.Text = ViewState("Barrio")
        txtTE.Text = ViewState("TE")
    End Sub

    Protected Sub CargarDDLNodos()
        Cnx = New OracleConnection(oradb)
        Sql = "select * from TBL_Nodos"
        DDLNodos.Items.Clear()
        Try
            Cnx.Open()
            Cmd = New OracleCommand(Sql, Cnx)
            Cmd.CommandType = Data.CommandType.Text
            Cmd.ExecuteNonQuery()
            dr = Cmd.ExecuteReader()
            DDLNodos.Items.Add("Su Elección")
            While dr.Read()
                DDLNodos.Items.Add(dr.Item("Descripcion"))
            End While
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        Finally
        End Try
        LiberarRecursos()
    End Sub
    Protected Sub ObtenerIdNodo()
        Cnx = New OracleConnection(oradb)
        Sql = "select * from TBL_Nodos WHERE Descripcion = '" & DDLNodos.SelectedValue & "'"
        Try
            Cnx.Open()
            Cmd = New OracleCommand(Sql, Cnx)
            Cmd.CommandType = Data.CommandType.Text
            Cmd.ExecuteNonQuery()
            dr = Cmd.ExecuteReader()
            If dr.Read() Then
                Aux = dr.Item("IdNodo")
            Else
                MsgBox("No puedo encontrar IdNodo en tabla TBL_Nodos" & vbCrLf, MsgBoxStyle.Critical, _
                       "Error proceso ObtenerIdNodo - Comisarias.aspx")
                LiberarRecursos()
                Exit Sub
            End If
        Catch ex As Exception
            MsgBox("No puedo abrir tabla TBL_Nodos" & vbCrLf & ex.Message, MsgBoxStyle.Critical, _
                   "Error proceso ObtenerIdNodo - Comisarias.aspx")
        Finally
        End Try
        LiberarRecursos()
    End Sub
    Protected Sub CargarDDLDescripcion_y_DDLComisariasEliminar()
        Cnx = New OracleConnection(oradb)
        Sql = "select * from Comisarias ORDER BY Descripcion ASC"
        DDLDescripcion.Items.Clear()
        DDLEliminar.Items.Clear()
        Try
            Cnx.Open()
            Cmd = New OracleCommand(Sql, Cnx)
            Cmd.CommandType = Data.CommandType.Text
            Cmd.ExecuteNonQuery()
            dr = Cmd.ExecuteReader()
            DDLDescripcion.Items.Add("Su Elección")
            DDLEliminar.Items.Add("Su Elección")
            While dr.Read()
                DDLDescripcion.Items.Add(dr.Item("Descripcion"))
                DDLEliminar.Items.Add(dr.Item("Descripcion"))
            End While
            DDLDescripcion.SelectedIndex = 0
            DDLEliminar.SelectedIndex = 0
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        Finally
        End Try
        LiberarRecursos()
    End Sub
    Protected Sub CargarDDLCampos()
        Cnx = New OracleConnection(oradb)
        Sql = "select * from Comisarias"
        DDLCampos.Items.Clear()
        Try
            Cnx.Open()
            Cmd = New OracleCommand(Sql, Cnx)
            Cmd.CommandType = Data.CommandType.Text
            Cmd.ExecuteNonQuery()
            dr = Cmd.ExecuteReader()
            DDLCampos.Items.Add("Su Elección")
            For x = 0 To dr.FieldCount - 1
                DDLCampos.Items.Add(dr.GetName(x))
            Next
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        Finally
        End Try
        LiberarRecursos()
    End Sub

    Protected Sub btnAceptar_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles btnAceptarEdicion.Click, btnAceptarPanelEliminar.Click
        If Page.IsValid Then
        Else
            labelMessage.Text = "Error en carga de datos - Proceso btnAceptar_Click - Comisarias.aspx"
            Exit Sub
        End If

        If txtDescripcion.Text.Length = 0 Then
            labelMessage.Text = "Descripcion : no puede ser NULL"
            Exit Sub
        End If
        If txtDomicilio.Text.Length = 0 Then
            labelMessage.Text = "Domicilio : no puede ser NULL"
            Exit Sub
        End If
        If txtBarrio.Text.Length = 0 Then
            labelMessage.Text = "Domicilio : no puede ser NULL"
            Exit Sub
        End If
        If txtTE.Text.Length = 0 Then
            labelMessage.Text = "TE : no puede ser NULL"
            Exit Sub
        End If
        If DDLNodos.SelectedIndex = 0 Then
            labelMessage.Text = "Falta seleccionar Nodo"
            Exit Sub
        End If


        Cnx = New OracleConnection(oradb)
        Cnx.Open()
        Select Case ViewState("Accion")
            Case "Editar"
                Try
                    Sql = "UPDATE Comisarias set " & _
                          "IdNodo='" & txtIdNodo.Text & "'," & _
                          "Domicilio='" & txtDomicilio.Text & "'," & _
                          "Barrio='" & txtBarrio.Text & "'," & _
                          "Descripcion='" & txtDescripcion.Text & "'," & _
                          "TE='" & txtTE.Text & _
                          "' WHERE IdComisaria='" & ViewState("IdComisaria") & "'"
                    Cmd = New OracleCommand(Sql, Cnx)
                    Cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("No puedo Actualizar (UPDATE) tabla Comisarias " & vbCrLf & ex.Message, MsgBoxStyle.Critical, _
                           "Error proceso btnAceptar_Click panel Edicion btnAceptarEdicion - Comisarias.aspx")
                Finally
                End Try
                LiberarRecursos()
                MostrarTablaComisarias()
                CargarDDLDescripcion_y_DDLComisariasEliminar()
                CargarDDLNodos()
            Case "Eliminar"
                If DDLEliminar.SelectedValue = "Su Elección" Then
                    lblCrtlDDLEliminar.Visible = True
                    Exit Sub
                Else
                    lblCrtlDDLEliminar.Visible = False
                End If
                Try
                    Sql = "DELETE FROM Comisarias WHERE Descripcion='" & DDLEliminar.SelectedValue & "'"
                    Cmd = New OracleCommand(Sql, Cnx)
                    Cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("No puedo ejecutar : DELETE FROM Comisarias WHERE Descripcion='" & DDLEliminar.SelectedValue & "'" & vbCrLf & ex.Message, MsgBoxStyle.Critical, _
                           "Error proceso btnAceptar_Click panel Eliminar btnAceptarPanelEliminar - Comisarias.aspx")
                Finally
                End Try
                LiberarRecursos()
                'DDLComisariasEliminar.Items.Remove(DDLComisariasEliminar.SelectedItem)
                'DDLComisariasEliminar.SelectedIndex = 0
                'DDLDescripcion.Items.Remove(DDLComisariasEliminar.SelectedItem)
                MostrarTablaComisarias()
                CargarDDLDescripcion_y_DDLComisariasEliminar()
                CargarDDLNodos()
            Case "Agregar"
                Try
                    Sql = "INSERT INTO Comisarias (IdNodo,Domicilio,Barrio,Descripcion,TE) VALUES (" & _
                          "'" & txtIdNodo.Text & "'," & _
                          "'" & txtDomicilio.Text & "'," & _
                          "'" & txtBarrio.Text & "'," & _
                          "'" & txtDescripcion.Text & "'," & _
                          "'" & txtTE.Text & "')"
                    Cmd = New OracleCommand(Sql, Cnx)
                    Cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("No puedo Agregar (INSERT INTO) tabla Comisarias " & vbCrLf & ex.Message, MsgBoxStyle.Critical, _
                           "Error proceso btnAceptar_Click panel Agregar btnAceptarEdicion - Comisarias.aspx")
                Finally
                End Try
                LiberarRecursos()
                LimpiarPanelEdicion()
                MostrarTablaComisarias()
                CargarDDLDescripcion_y_DDLComisariasEliminar()
                CargarDDLNodos()
        End Select
        PanelFiltro.Visible = False
        PanelEliminar.Visible = False
        PanelEdicion.Visible = False
    End Sub

    Protected Sub DDLCampos_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles DDLCampos.SelectedIndexChanged
        If DDLCampos.SelectedValue = "Su Elección" Then
            Exit Sub
        End If
        Cnx = New OracleConnection(oradb)
        Try
            Cnx.Open()
            Sql = "SELECT DISTINCT " & DDLCampos.SelectedValue & " FROM Comisarias ORDER BY " & DDLCampos.SelectedValue & " ASC"
            Cmd = New OracleCommand(Sql, Cnx)
            DDLDatos.Items.Clear()
            DDLDatos.Items.Add("Su Elección")
            dr = Cmd.ExecuteReader()
            While dr.Read()
                DDLDatos.Items.Add(dr.Item(0))
            End While
        Catch ex As Exception
            MsgBox("No puedo abrir recordset = SELECT DISTINCT " & DDLCampos.SelectedValue & " FROM Comisarias ORDER BY " & DDLCampos.SelectedValue & " ASC" & vbCrLf & ex.Message, MsgBoxStyle.Critical, _
                   "Error proceso DDLCampos_SelectedIndexChanged - Comisarias.aspx")
        Finally
        End Try
        LiberarRecursos()
        txtSQL.Text = "SELECT * FROM Comisarias WHERE Comisarias." & DDLCampos.SelectedValue & " "
    End Sub
    Protected Sub DDL_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles DDLNodos.SelectedIndexChanged
        If sender.SelectedValue = "Su Elección" Then
            Exit Sub
        End If
        Select Case sender.id
            Case "DDLNodos"
                ObtenerIdNodo()
                txtIdNodo.Text = Aux
        End Select
    End Sub
    Protected Sub btnAceptarFiltro_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAceptarFiltro.Click
        Cnx = New OracleConnection(oradb)
        Sql = "select * from COMISARIAS"
        Try
            Cnx.Open()
            Cmd = New OracleCommand(txtSQL.Text, Cnx)
            Cmd.CommandType = Data.CommandType.Text
            dr = Cmd.ExecuteReader()
            GV2.DataSource = dr
            GV2.DataBind()
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        Finally
        End Try
        LiberarRecursos()
    End Sub

    Protected Sub DDLFiltro_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles DDLLogicos.SelectedIndexChanged, DDLDatos.SelectedIndexChanged
        If DDLCampos.SelectedValue = "Su Elección" Then
            MsgBox("Seleccione que campo usar." & vbCrLf, MsgBoxStyle.Critical, "Error proceso DDLFiltro_SelectedIndexChanged - Comisarias.aspx")
            lblCampo.Visible = True
            Exit Sub
        End If
        lblCampo.Visible = False
        If DDLLogicos.SelectedValue = "Su Elección" Then
            MsgBox("Seleccione que operador lógico usar." & vbCrLf, MsgBoxStyle.Critical, _
                   "Error proceso DDLFiltro_SelectedIndexChanged - Comisarias.aspx")
            lblLogico.Visible = True
            Exit Sub
        End If
        Select Case sender.id
            Case "DDLLogicos"
                lblLogico.Visible = False
                txtSQL.Text = txtSQL.Text & " " & DDLLogicos.SelectedValue
            Case "DDLDatos"
                lblDato.Visible = False
                txtSQL.Text = txtSQL.Text & " '" & DDLDatos.SelectedValue & "' "
        End Select
    End Sub

    Protected Sub btnLimpiarSQL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLimpiarSQL.Click
        txtSQL.Text = ""
        DDLCampos.SelectedIndex = 0
        DDLLogicos.SelectedIndex = 0
        DDLDatos.SelectedIndex = 0
    End Sub

    Protected Sub DDLComisariasEliminar_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLEliminar.SelectedIndexChanged
        If DDLEliminar.SelectedValue = "Su Elección" Then
            Exit Sub
        End If
    End Sub

    Protected Sub AceptarPanelSeleccion(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAceptarSeleccion.Click
        If DDLDescripcion.SelectedValue = "Su Elección" Then
            lblCrtlDDLDescripcion.Visible = True
            PanelEdicion.Visible = False
            Exit Sub
        Else
            lblCrtlDDLDescripcion.Visible = False
            PanelEdicion.Visible = True
        End If
        PanelEdicion.Visible = True
        Cnx = New OracleConnection(oradb)
        Sql = "select * from COMISARIAS WHERE DESCRIPCION = '" & DDLDescripcion.SelectedValue & "'"
        Try
            Cnx.Open()
            Cmd = New OracleCommand(Sql, Cnx)
            Cmd.CommandType = Data.CommandType.Text
            dr = Cmd.ExecuteReader()
            dr.Read()
            ViewState("IdComisaria") = dr.Item("IdComisaria")
            txtDescripcion.Text = dr.Item("Descripcion") : ViewState("Descripcion") = dr.Item("Descripcion")
            txtIdNodo.Text = dr.Item("IdNodo") : ViewState("IdNodo") = dr.Item("IdNodo")
            txtDomicilio.Text = dr.Item("Domicilio") : ViewState("Domicilio") = dr.Item("Domicilio")
            txtBarrio.Text = dr.Item("Barrio") : ViewState("Barrio") = dr.Item("Barrio")
            txtTE.Text = dr.Item("TE") : ViewState("TE") = dr.Item("TE")
        Catch ex As Exception
            MsgBox("No puedo abrir tabla COMISARIAS " & vbCrLf & ex.Message, MsgBoxStyle.Critical, _
                   "Error proceso AceptarPanelSeleccion - Comisarias.aspx")
            MsgBox(ex.Message.ToString())
        Finally
        End Try
        LiberarRecursos()
    End Sub

    Protected Sub btnCalcWin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCalcWin.Click
        Dim Proceso As New Diagnostics.Process()
        Proceso.StartInfo.FileName = "calc.exe"
        Proceso.StartInfo.Arguments = ""
        Proceso.Start()
    End Sub
End Class
